#encoding: utf-8

=begin
    用于产生渠道经营报表
    ruby report-spreader.rb 2021-07-01 2021-07-31 2021-06-01 2021-06-30
=end

load 'rds_api.rb'
@rds=@rds_tt

load 'tutem_utils.rb'

if ARGV.size < 4
    puts "Usage: ruby report-spreader.rb 2021-07-01 2021-07-31 2021-06-01 2021-06-30" 
    exit
end

@sday = ARGV[0].nil? ? Date.today : Date.parse(ARGV[0])
@eday = ARGV[1].nil? ? Date.today : Date.parse(ARGV[1])
@sday0 = ARGV[2].nil? ? Date.today : Date.parse(ARGV[2])
@eday0 = ARGV[3].nil? ? Date.today : Date.parse(ARGV[3])

stime = unixtime_of_sday @sday
etime = unixtime_of_eday @eday
stime0 = unixtime_of_sday @sday0
etime0 = unixtime_of_eday @eday0

def get_refund_channel_uids
    return [1782] #刘LYT
end

def get_user_orders_by_suid suid

    #获取用户基础信息：ID, 姓名，头像，注册时间
    sql = "select uid, concat(real_name,'(',nickname,')') as name, avatar, add_time from tutem_mall.eb_user_extended where spread_uid = #{suid}"
    uids = []
    users = {}
    queryRds(sql).each do |q| 
        uid = q['uid']

        add_time = q['add_time']
        add_time = Time.at(add_time).to_datetime
        add_time = add_time.strftime('%Y-%m-%d')
        q.store 'created_at', add_time

        uids += [uid] 
        users.store uid, q
    end


    #获取用户有效购买的订单ids，订单地址, 购买次数，最早购买时间，最后购买时间
    bids = [] 
    buyers = {}
    uids.each do |uid|

        buyer = {
            "uid" => uid,
            "name" => users[uid]['name'],
            "created_at" => users[uid]['created_at'],
            "oids" => [],
            "phone" => 'n/a',
            "addr" => 'n/a',
            "order_count" => 0,
            "total_paid" => 0.0,
            "total_deduction" => 0.0,
            "paid_postage" => 0.0,
            "paid_goods" => 0.0,
            "first_time" => 'n/a',
        }


        pcodes = typical_goods_codes + ['OTHR']
        pcodes.each { |pc| buyer.store pc, 0.0 }

        sql = "select id, user_address as addr, user_phone as phone, sum(deduction_price) as total_deduction, sum(pay_price) as total_paid, sum(pay_postage) as paid_postage, count(*) as order_count, min(pay_time) as first_time from tutem_mall.eb_store_order where paid = 1 and uid = #{uid}"
        queryRds(sql).each do |q| 
            if q['id'].to_s != ''

                oids = buyer['oids']
                oids += [q['id']]
                buyer.store 'oids', oids

                buyer.store 'phone', q['phone']
                buyer.store 'addr', q['addr'].gsub(' ','')
                buyer.store 'order_count', q['order_count']
                buyer.store 'total_paid', q['total_paid']
                buyer.store 'paid_postage', q['paid_postage']
                buyer.store 'paid_goods', q['total_paid'] - q['paid_postage']
                buyer.store 'total_deduction', q['total_deduction']
                
                first_time = q['first_time']
                first_time = Time.at(first_time).to_datetime
                first_time = first_time.strftime('%Y-%m-%d')
                buyer.store 'first_time', first_time

                bids += [uid]
            end
        end

        if buyer['order_count'] > 0 
            oids = buyer['oids']
            info = getGoodsQtyForOrders(oids);
            info.each do |pcode, sales|
                buyer.store pcode, sales
            end

            #puts "#{uid} #{buyer['name']} #{buyer['created_at']} #{buyer['first_time']} #{buyer['order_count']} #{buyer['B560']} #{buyer['1680']} #{buyer['B298']} #{buyer['198']} #{buyer['addr']} #{buyer['phone']}"

            buyers.store uid, buyer
            bids = bids.uniq

        end

    end


    #获取用户购买次数及最早购买时间，最后购买时间

    return {
        'uids' => uids,
        'users' => users,
        'bids' =>  bids,
        'buyers' => buyers
    }

end

def gen_report_by_suid suid

    sname = '推广用户'
    sql = "select real_name from tutem_mall.eb_user_extended where uid = #{suid}"
    queryRds(sql).each do |q|
        sname = q['real_name']
        break
    end

    guo = get_user_orders_by_suid suid

    uids = guo['uids']

    bids = guo['bids']
    buyers = guo['buyers']

    buyer_items = []
    buyers.each do |bid, buyer|
        buyer_items += [buyer]
    end

    headers = ["uid","name","created_at","first_time","order_count","total_paid","total_deduction","B560","B298","B198","B1680","addr","phone" ] 
    names = ["uid","name","created_at","first_time","order_count","total_paid","total_deduction","B560","B298","B198","B1680","addr","phone" ] 
    #names = ["#","姓名","注册日期","首次购买","订单总数","支付总额","其中运费","560数","298数","198数","1680","地址","电话" ] 
    save_to_excel headers,names,buyer_items,"spreader-#{sname}-#{suid}.xls"

end

#TBO 等待用来做推广渠道分析,上月和本月同比
#[6,26,59,32,1,5,4,633].each { |suid| gen_report_by_suid suid }

def get_active_spreaders stime, etime
  uids_1 = get_active_spreaders_by_orders stime, etime
  uids_2 = get_active_spreaders_by_fans stime, etime 
  return (uids_1 + uids_2).uniq
end

def get_active_spreaders_by_fans stime, etime
    sql = "select spread_uid from tutem_mall.eb_user where uid in (select uid from tutem_mall.eb_user where add_time >= #{stime} and add_time <= #{etime}) and is_promoter = 1 group by spread_uid" #只统计有渠道标记的数据
    sql = "select spread_uid from tutem_mall.eb_user where uid in (select uid from tutem_mall.eb_user where add_time >= #{stime} and add_time <= #{etime}) group by spread_uid"
    res = queryRds sql
    uids = [];
    res.each do |r| 
      uids += [r['spread_uid']]
    end
    return uids
end

def get_active_spreaders_by_orders stime, etime
    sql = "select spread_uid from tutem_mall.eb_user where uid in (select uid from tutem_mall.eb_store_order where paid > 0 and pay_time >= #{stime} and pay_time <= #{etime}) group by spread_uid"
    res = queryRds sql
    uids = [];
    res.each do |r| 
      uids += [r['spread_uid']]
    end
    return uids
end

def show_spreader_workout uid, stime, etime, stime0, etime0

    info = {}
    info.store "uid", uid
    info.store "name", userList[uid]['name']
    info.store "aname", userList[uid]['aname']
    info.store "phone", userList[uid]['phone']
    info.store "aphone", userList[uid]['aphone']
    info.store "agency", userList[uid]['agency']
    info.store "department", userList[uid]['department']
    info.store "is_promoter", userList[uid]['is_promoter']
    info.store "promoter_type", userList[uid]['promoter_type']

    #增加销售代表代码
    rep_code = 'UNS'
    sql = "select rep_code from tutem_mall.eb_user where uid = #{uid}"
    res = queryRds sql
    res.each { |r| rep_code = r['rep_code'].to_s }
    info.store 'rep_code', rep_code

    #不包括图贝、积分的销售总额
    amountT = 0.00
    sql = "select (sum(total_price)+sum(total_postage)-sum(coupon_price)-sum(deduction_price)-sum(refund_price)-sum(back_integral)/100) as amount from tutem_mall.eb_store_order where pay_time > #{stime} and pay_time < #{etime} and pay_type = 'weixin' and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})" #bug-2023-04-09-1
    sql = "select (sum(pay_price)-sum(refund_price)) as amount from tutem_mall.eb_store_order where pay_time > #{stime} and pay_time < #{etime} and pay_type = 'weixin' and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})"
    res = queryRds sql
    res.each { |r| amountT = r['amount'] }
    amountT = 0.00 if amountT.nil?

    amountL = 0.00
    sql = "select (sum(total_price)+sum(total_postage)-sum(coupon_price)-sum(deduction_price)-sum(refund_price)-sum(back_integral)/100) as amount from tutem_mall.eb_store_order where pay_time >#{stime0} and pay_time <#{etime0} and pay_type = 'weixin' and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})"
    sql = "select (sum(pay_price)-sum(refund_price)) as amount from tutem_mall.eb_store_order where pay_time >#{stime0} and pay_time <#{etime0} and pay_type = 'weixin' and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})"
    res = queryRds sql
    res.each { |r| amountL = r['amount'] }
    amountL = 0.00 if amountL.nil?

    amountC = amountL == 0 ? 'n/a' : "#{(((amountT-amountL)/amountL).to_f.round(2)*100).to_i}%"

    info.store "amountT", amountT
    info.store "amountL", amountL
    info.store "amountC", amountC

    #新增推荐
    sql = "select count(*) as new_fans from tutem_mall.eb_user where add_time > #{stime} and add_time < #{etime} and spread_uid = #{uid}"
    res = queryRds sql
    res.each { |r| info.store "newfansT", r['new_fans'] }

    sql = "select count(*) as new_fans from tutem_mall.eb_user where add_time > #{stime0} and add_time < #{etime0} and spread_uid = #{uid}"
    res = queryRds sql
    res.each { |r| info.store "newfansL", r['new_fans'] }

    newfansL = info["newfansL"]
    newfansT = info["newfansT"]
    newfansC = newfansL == 0 ? 'n/a' : "#{(newfansT-newfansL)*100/newfansL}%"
    info.store "newfansC", newfansC

    #推荐成功
    sql = "select uid from tutem_mall.eb_store_order where uid in (select uid from tutem_mall.eb_user where add_time > #{stime} and add_time < #{etime} and spread_uid = #{uid}) and pay_time > #{stime} and pay_time < #{etime} group by uid"
    res = queryRds sql
    convincedT=0
    res.each { |r| convincedT += 1 }
    info.store "convincedT", convincedT

    sql = "select uid from tutem_mall.eb_store_order where uid in (select uid from tutem_mall.eb_user where add_time > #{stime0} and add_time < #{etime0} and spread_uid = #{uid}) and pay_time > #{stime0} and pay_time < #{etime0}  group by uid"
    res = queryRds sql
    convincedL=0
    res.each { |r| convincedL += 1 }
    info.store "convincedL", convincedL

    convincedL = info["convincedL"]
    convincedT = info["convincedT"]
    convincedC = convincedL == 0 ? 'n/a' : "#{(convincedT-convincedL)*100/convincedL}%"
    info.store "convincedC", convincedC

    oidsT = []
    sql = "select id from tutem_mall.eb_store_order where pay_time > #{stime} and pay_time < #{etime} and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})"
    (queryRds sql).each { |r|  oidsT += [r['id']] }
    pcodesT = getGoodsPaidInWeixinForOrders oidsT

    oidsL = []
    sql = "select id from tutem_mall.eb_store_order where pay_time > #{stime0} and pay_time < #{etime0} and uid in (select uid from tutem_mall.eb_user where spread_uid = #{uid})"
    (queryRds sql).each { |r|  oidsL += [r['id']] }
    pcodesL = getGoodsPaidInWeixinForOrders oidsL

    goods = typical_goods_codes + ['OTHR']
    goods.each do |pcode|

        tagT = "#{pcode}-T"
        tagL = "#{pcode}-L"
        tagC = "#{pcode}-C"

        salesT = pcodesT[pcode].nil? ? 0 : pcodesT[pcode]
        salesL = pcodesL[pcode].nil? ? 0 : pcodesL[pcode]
        salesC = salesL == 0 ? 'n/a' : "#{(salesT-salesL)*100/salesL}%"

        info.store tagT, salesT
        info.store tagL, salesL
        info.store tagC, salesC

    end

    return info

end

def not_been_incentived uid, sday, eday
    link_info = "I1-#{sday}~#{eday}" 
    sqls = "select count(*) as recs from tutem_mall.eb_user_bill where uid = #{uid} and link_id = '#{link_info}'"
    res = queryRds sqls
    res.each do |r|
        ap r
        return r['recs'] == 0 ? true : false
    end
    return false
end

def incentive_spreader_during uid, stime, etime

    puts "runing totem sales incentive program for user ##{uid} during #{@sday} ~ #{@eday}"

    puts "1) bonus records as below:"
    sql = "select link_id, title, mark, number as bonus, balance, from_unixtime(add_time) as confirmed_at from tutem_mall.eb_user_bill where uid = #{uid} and add_time > #{stime} and add_time < #{etime} and category = 'now_money' and type = 'brokerage' and pm = 1";
    res = queryRds sql
    res.each do |r|
        ap r
    end

    sql = "select sum(number) as total_bonus from tutem_mall.eb_user_bill where uid = #{uid} and add_time > #{stime} and add_time < #{etime} and category = 'now_money' and type = 'brokerage' and pm = 1";
    res = queryRds sql
    incentive = 0
    res.each do |r|
        incentive = r['total_bonus']
        break
    end
    puts "total bonus: #{incentive.to_f.round(2)}"

    sql = "select now_money from tutem_mall.eb_user where uid = #{uid}"
    balance = 0
    res = queryRds sql
    res.each do |r|
        balance = r['now_money']
        break
    end
    current_tupay = balance.to_f.round(2)
    puts "2) current tupay balance: #{current_tupay}"

    rebate = ( incentive * 0.5 ).to_f.round(2)
    balance = (current_tupay + rebate).round(2).to_f
    puts "3) applying sales incentive program 20210711L (50% bonus rebate)"
    puts "rebate total: #{rebate}"

    if not_been_incentived uid, @sday, @eday
        sqls = []
        sqls += ["update tutem_mall.eb_user set now_money = #{balance} where uid = #{uid}"]
        msg = "#{@sday}~#{@eday}期间共获#{incentive.round(2).to_f}图贝，额外奖励#{rebate}枚图贝"
        link_info = "I1-#{@sday}~#{@eday}"
        add_time = Time.now.to_i
        sqls += ["INSERT INTO tutem_mall.eb_user_bill ( uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take) VALUES ( #{uid}, '#{link_info}', 1, '附加奖励政策20210711L', 'now_money', 'brokerage', #{rebate}, #{balance}, '#{msg}', #{add_time}, 1, 0);"]
        commitTrans sqls
    end

    puts "4) recording tupay account as below:"
    sql = "select id, title, mark, number as bonus, balance, from_unixtime(add_time) as created_at from tutem_mall.eb_user_bill where uid = #{uid} and category = 'now_money' and type = 'brokerage' and pm = 1 order by id desc limit 1";
    res = queryRds sql
    incentive = 0
    res.each do |r|
        ap r
    end

    sql = "select now_money from tutem_mall.eb_user where uid = #{uid}"
    balance = 0
    res = queryRds sql
    res.each do |r|
        balance = r['now_money']
        break
    end
    current_tupay = balance.to_f.round(2)
    puts "5) new tupay balance: #{current_tupay}"

end

def key_to_header key
    case key
    when 'amountT'
        return '本月销售'
    when 'amountL'
        return '上月'
    else
        return key
    end
end

def create_compared_workout stime, etime, stime0, etime0

    spreaders = get_active_spreaders stime0, etime
    #spreaders = [3277]
    items = []
    spreaders.each do |uid|
        info = show_spreader_workout uid, stime, etime, stime0, etime0
        items += [info]
    end

    if items.size > 0

        #第一个记录抓出来产生表头
        headers = []
        names = []
        items[0].each do |key, value|
            header = key_to_header key
            headers += [key]
            names += [key]
        end

        save_to_excel headers,names,items,"channel-report-#{@sday}~#{@eday}.xls"
    end

end

#incentive_spreader_during 1782, stime, etime

create_compared_workout stime, etime,stime0, etime0
